package com.ebraga.trackerclub.android.util.core;

import java.util.Date;

import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.ebraga.trackerclub.android.util.DLog;
import com.ebraga.trackerclub.android.util.api.AndroidContext;
import com.ebraga.trackerclub.android.util.api.AndroidSQLiteDatabase;
import com.ebraga.trackerclub.android.util.enumeration.AndroidConfigurationParameterName;


class DatabaseHelper extends SQLiteOpenHelper {

	private static final String TAG = DatabaseHelper.class.toString();

    public static final String DB_NAME 	= "tracker_db";
    public static final int VERSION 	= 1;

	public DatabaseHelper(AndroidContext context) {
		
		super(context.getApplicationContext(), DB_NAME, null, VERSION);
	}

	/**
	 * Create tables
	 * 
	 * Remark: 
	 * _ID: auto incremented number
	 * ID: sys_id from Service-now (String)
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		DLog.d(TAG, "Creating tables");
	    
		db.execSQL("CREATE TABLE user (" +
				"imei TEXT PRIMARY KEY NOT NULL, " +
				"name TEXT NOT NULL, " +
				"brand TEXT NOT NULL, " +
				"model TEXT NOT NULL, " +
				"lastLogin LONG NOT NULL);");
		db.execSQL("CREATE TABLE parameter (" +
				"xidParameter INTEGER PRIMARY KEY AUTOINCREMENT, " +
				"name TEXT NOT NULL, " +
				"value TEXT NOT NULL, " +
				"description TEXT NOT NULL);");
		db.execSQL("CREATE TABLE position (" +
				"xidPosition INTEGER PRIMARY KEY AUTOINCREMENT, " +
				"imei TEXT NOT NULL, " +
				"latitude NUMERIC NOT NULL, " +
				"longitude NUMERIC NOT NULL, " +
				"accuracy NUMERIC NOT NULL, " +
				"speed NUMERIC NOT NULL, " +
				"provider TEXT NOT NULL, " +
				"dateCreated LONG NOT NULL, " +
				"sinchronized LONG);");
		db.execSQL("CREATE TABLE checkInOut (" +
				"xidCheckInOut INTEGER PRIMARY KEY AUTOINCREMENT, " +
				"imei TEXT NOT NULL, " +
				"dateCreated LONG NOT NULL, " +
				"type TEXT NOT NULL, " +
				"idPosition INTEGER NOT NULL, " +
				"sinchronized LONG);");
		db.execSQL("CREATE TABLE syncData (" +
				"xidSyncData INTEGER PRIMARY KEY AUTOINCREMENT, " +
				"name TEXT NOT NULL," +
				"lastTryDateTime LONG, " +
				"lastSuccessDateTime LONG);");
		db.execSQL("CREATE TABLE ranking (" +
				"xidRanking INTEGER PRIMARY KEY AUTOINCREMENT, " +
				"name TEXT NOT NULL, " +
				"brand TEXT, " +
				"model TEXT NOT NULL, " +
				"latitude NUMERIC NOT NULL, " +
				"longitude NUMERIC NOT NULL, " +
				"speed NUMERIC NOT NULL, " +
				"date LONG NOT NULL);");		
		
		DLog.d(TAG, "Populating data");
		loadInitialData(db);
		
	}
	
	private void loadInitialData(SQLiteDatabase db) {
		
		ContentValues initialValues = new ContentValues();
        initialValues.put("name", AndroidConfigurationParameterName.LOGIN_OFFLINE_EXPIRE.getName());
        initialValues.put("value", "10080");
        initialValues.put("description", "Intervalo permitido para login offline.");
        db.insert("parameter", null, initialValues);
        
		initialValues = new ContentValues();
        initialValues.put("name", AndroidConfigurationParameterName.LOGIN_OFFLINE_AUTO.getName());
        initialValues.put("value", "0");
        initialValues.put("description", "Intervalo permitido para login automático.");
        db.insert("parameter", null, initialValues);        
        
        initialValues = new ContentValues();
        initialValues.put("name", AndroidConfigurationParameterName.POSITION_TIME.getName());
        initialValues.put("value", "10");
        initialValues.put("description", "Intervalo de atualização do GPS.");
        db.insert("parameter", null, initialValues);
        
        initialValues = new ContentValues();
        initialValues.put("name", AndroidConfigurationParameterName.SYNC_OFFSET_IN_MINUTES.getName());
        initialValues.put("value", "1");
        initialValues.put("description", "Intervalo de sincronismo dos dados.");
        db.insert("parameter", null, initialValues);
        
        initialValues = new ContentValues();
        initialValues.put("name", AndroidConfigurationParameterName.GPS_ACCEPTABLE_ACCURACY.getName());
        initialValues.put("value", "500");
        initialValues.put("description", "Precisão aceitável");
        db.insert("parameter", null, initialValues);        
        
        initialValues = new ContentValues();
        initialValues.put("name", AndroidConfigurationParameterName.VERSION_APP.getName());
        initialValues.put("value", "1.0");
        initialValues.put("description", "Versão do aplicativo mobile");
        db.insert("parameter", null, initialValues);     
        
        
        initialValues = new ContentValues();
        initialValues.put("name", AndroidConfigurationParameterName.BANNER_ENABLED.getName());
        initialValues.put("value", "FALSE");
        initialValues.put("description", "Habilitado Banner de Prograganda.");
        db.insert("parameter", null, initialValues);            
        
        initialValues = new ContentValues();
        initialValues.put("name", AndroidConfigurationParameterName.SYNC_OFFSET_IN_MINUTES.getName());
        initialValues.put("lastTryDateTime", new Date().getTime());
        initialValues.put("lastSuccessDateTime", new Date().getTime());
        db.insert("syncData", null, initialValues);

	}

	/**
	 * Make changes to tables when version changes
	 * 
	 * For now: simple recreate
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVer, int newVer) {
		DLog.d(TAG, "Updating tables (drop and re-create)");

		// DROP all tables // TODO: except "messages"?
		db.execSQL("DROP TABLE IF EXISTS user");
		db.execSQL("DROP TABLE IF EXISTS parameter");
		db.execSQL("DROP TABLE IF EXISTS position");
		db.execSQL("DROP TABLE IF EXISTS checkInOut");
		db.execSQL("DROP TABLE IF EXISTS syncData");
		db.execSQL("DROP TABLE IF EXISTS ranking");
		
		// Recreate tables
		onCreate(db);
	}
	
	
	public AndroidSQLiteDatabase getAndroidWritableDatabase(){
		AndroidSQLiteDatabaseImpl androidSql = new AndroidSQLiteDatabaseImpl();
		if(System.getProperty("Enviroment")==null || !System.getProperty("Enviroment").equals("UNIT_TEST")){
			androidSql.setSQLLiteDataBase(super.getWritableDatabase());
		}
		return androidSql;
	}

}
